package com.ftg.learn.springboot.brand.mapper;


import com.ftg.learn.springboot.brand.pojo.brand;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface brandMapper {

	@Select("select * from brand where bcategory=ifnull(#{bcategory},bcategory)")
	List<brand> show(String bcategory);

	@Select("select * from brand")
	List<brand> showall();

	@Select("SELECT * FROM brand ORDER BY bheat DESC LIMIT 4")
	List<brand> showByHeat();

	@Select("SELECT * FROM brand where  bcategory=ifnull(#{bcategory},bcategory) ORDER BY bheat DESC LIMIT 0,1")
	brand findFirstHeat(String bcategory);

	@Select("SELECT * FROM brand where  bcategory=ifnull(#{bcategory},bcategory) ORDER BY bheat DESC LIMIT 1,1")
	brand findSecondHeat(String bcategory);

	@Select("select PV from pageview where id = 1")
	String showPV();

	//数个数
	@Select("select count(bid) from brand ")
	int countall();


	@Select("SELECT * FROM brand where  bcategory=ifnull(#{bcategory},bcategory) ORDER BY bheat DESC LIMIT 2,1")
	brand findThirdHead(String bcategory);

	@Select("SELECT * FROM brand where  bcategory=ifnull(#{bcategory},bcategory) ORDER BY bheat DESC LIMIT 3,1")
	brand findFourthHead(String bcategory);

	@Select("SELECT * FROM brand ORDER BY enjoy DESC LIMIT 3")
	List<brand> showByEnjoy();

	@Update("UPDATE brand SET enjoy=enjoy+1 WHERE bid = #{id}")
	int dianzan(String id);

	@Select("SELECT blogo,cid,binfo,enjoy,bwebsite,c.bid,bname,COUNT(c.`bid`) COUNT,pls FROM brand b RIGHT JOIN COMMENT c ON b.bid=c.bid LEFT JOIN (SELECT bid,COUNT(bid) pls FROM COMMENT GROUP BY bid) d ON c.bid=d.bid WHERE c.star > 3.5 GROUP BY c.bid ORDER BY COUNT DESC LIMIT 3")
	List<brand> showExcellent();

	@Update("UPDATE brand SET enjoy=enjoy-1 WHERE bid = #{id}")
	int quxiaodianzan(String id);
}
